// const userController = require("./controllers/userController") 
const { db, query } = require("../database/db")

// 0. 小程序中的登录
// 0.1 首先检测当前用户是否登录过
exports.existingUser = (openid) => {
    let sql = `
        SELECT * FROM users
        WHERE openid = ? AND users.del_status = '0'
    `
    return query(sql, [openid])
}

// 0.2 接着向数据库中添加这个未注册人的openid
exports.register = (phone, openid) => {
    let sql = `
        INSERT INTO users (phone,openid) VALUES (?,?)
    `
    return query(sql, [phone, openid])
}

// 1. 登录
exports.login = (phone) => {
    let search_sql = `
        SELECT * FROM users
        WHERE phone = ? AND users.del_status = '0'
    `
    return query(search_sql, [phone])
}

// 2. 用户的列表+搜索页
exports.userlist = async (page, pageSize, username, realname, phone) => {
    // 基础SQL查询语句，使用JOIN连接users表和roles表
    let listSql = `
        SELECT 
            id,
            username,
            address,
            age,
            gender,
            realname,
            hobby,
            phone,
            del_status,
            role,
            userUrl
        FROM users`;
    // 初始化参数数组
    let params = [];

    // 如果有搜索条件，添加到SQL语句中
    let conditions = [`users.del_status = '0'`]; // 默认条件，用户状态为0
    if (realname) {
        conditions.push(`users.realname LIKE ?`);
        params.push(`%${realname}%`);
    }
    if (username) {
        conditions.push(`users.username LIKE ?`);
        params.push(`%${username}%`);
    }
    if (phone) {
        conditions.push(`users.phone LIKE ?`);
        params.push(`%${phone}%`);
    }

    // 如果有搜索条件，添加WHERE子句
    if (conditions.length > 0) {
        listSql += ` WHERE ${conditions.join(' AND ')}`;
    }

    // 添加分页的SQL语句
    listSql += ` LIMIT ? OFFSET ?`;
    // (page - 1) * pageSize, page * pageSize
    // 添加分页参数
    params.push(pageSize);
    params.push((page - 1) * pageSize);

    // 计算总数的SQL语句
    let countSql = `SELECT COUNT(*) as count FROM users`;
    if (conditions.length > 0) {
        countSql += ` WHERE ${conditions.map(c => `(${c})`).join(' AND ')}`;
    }

    // 执行查询
    const [usersResult, countResult] = await Promise.all([
        query(listSql, params), // 查询用户列表
        query(countSql, params) // 查询总数
    ]);
    return {
        users: usersResult, // 用户列表
        total: countResult // 总数
        // total: countResult.total[0].count
    };
};

// 3.用户的删除---更改记录
exports.Fdelte = async (id, now) => {
    let sql = `
        UPDATE users SET del_status = 1, deleteTime = ?
        WHERE id = ?
    `
    return query(sql, [now, id])
}

// 4.用户的删除---删除记录
exports.Tdelte = async (id) => {
    let sql = `
        DELETE FROM users
        WHERE id = ?
    `
    return query(sql, [id])
}

// 5.用户列表的查看数据
exports.get = async (id) => {
    let sql = `
        SELECT 
            id,
            username,
            address,
            age,
            gender,
            realname,
            hobby,
            phone,
            del_status,
            role,
            userUrl
        FROM users
        WHERE id = ? AND del_status = '0'
    `
    return query(sql, [id])
}

// 6.用户列表的更新数据---更新记录
exports.set = async (id, username, address, age, gender, realname, hobby, phone, now_dateTime, userUrl, role) => {
    let sql = `
        UPDATE users 
        SET username = ?, address = ?, age = ?, gender = ?, realname = ?, hobby = ?, phone = ?, updateTime = ? , userUrl = ?,
            role = ?
        WHERE id = ?
    `
    return query(sql, [username, address, age, gender, realname, hobby, phone, now_dateTime, userUrl, role, id])
}

// 7.统计图数据接口---活动热度表---统计每个活动的热度
exports.hotChart = async () => {
    let sql = `
        SELECT name, SUM(views) AS total_views
        FROM actives
        GROUP BY name
        ORDER BY total_views DESC;
    `
    return query(sql)
}

// 8.统计图数据接口---用户活动表---统计每个用户报名的次数
exports.userSign = async () => {
    let sql = `
        SELECT 
            users.username, 
            users.id as user_id, 
            COUNT(active_users.user_id) AS active_count  -- 统计关联记录数
        FROM users
        LEFT JOIN active_users ON users.id = active_users.user_id  -- 关联用户与活动记录
        GROUP BY users.username, users.id  -- 按用户分组
        ORDER BY active_count DESC;
    `
    return query(sql)
}

// 9.统计图数据接口---用户贡献表---统计每个用户发布活动的占比
exports.userPublish = async () => {
    const sql = `
        SELECT 
            users.username, 
            users.id as user_id, 
            COUNT(actives.publisher_id) AS publish_count,
            CASE 
                WHEN (SELECT COUNT(*) FROM actives) = 0 THEN 0
                ELSE ROUND(COUNT(actives.publisher_id) * 1.0 / (SELECT COUNT(*) FROM actives), 2)
            END AS publish_ratio
        FROM users
        LEFT JOIN actives ON users.id = actives.publisher_id
        GROUP BY users.username, users.id
        ORDER BY publish_count DESC;
    `;
    let all_active_sql = `
        SELECT 
            COUNT(*) AS all_active
        FROM actives;
    `
    const results = await query(sql);
    const all_active_res = await query(all_active_sql)

    const all_active = all_active_res.length > 0 ? all_active_res[0].all_active : 0;
    
    return {
        results: results,
        all_active: all_active
    };
};
